package com.yungu.swift.admin.route.operation;

import com.alibaba.dubbo.config.annotation.Reference;
import com.fasterxml.jackson.databind.JsonNode;
import com.yungu.swift.admin.base.CurrentUser;
import com.yungu.swift.admin.consts.AdminErrorEnum;
import com.yungu.swift.admin.route.helper.DataListHelper;
import com.yungu.swift.admin.utils.DownloadUtils;
import com.yungu.swift.admin.utils.RandomCipherUtils;
import com.yungu.swift.admin.utils.UploadUtils;
import com.yungu.swift.admin.utils.WorkbookUtil;
import com.yungu.swift.assets.driver.model.dto.CarDto;
import com.yungu.swift.assets.driver.model.dto.DriverCommissionConfigDto;
import com.yungu.swift.assets.driver.model.dto.DriverDto;
import com.yungu.swift.assets.driver.model.dto.DriverTrainDto;
import com.yungu.swift.assets.driver.model.param.*;
import com.yungu.swift.assets.driver.model.vo.AdminDriverVo;
import com.yungu.swift.assets.driver.model.vo.DriverExportVo;
import com.yungu.swift.assets.driver.model.vo.DriverImportVo;
import com.yungu.swift.assets.driver.service.DriverService;
import com.yungu.swift.autoconfig.redis.RedisStringCacheService;
import com.yungu.swift.base.ResponseData;
import com.yungu.swift.base.model.PageVo;
import com.yungu.swift.base.model.admin.AdminParam;
import com.yungu.swift.base.model.annotation.ResponseImgOverride;
import com.yungu.swift.constants.CommonConstant;
import com.yungu.swift.constants.DriverConstant;
import com.yungu.swift.constants.MagicConstant;
import com.yungu.swift.system.sys.model.dto.SysBusinessDto;
import com.yungu.swift.system.sys.model.dto.SysCityDto;
import com.yungu.swift.system.sys.model.dto.SysCompanyDto;
import com.yungu.swift.system.sys.model.dto.SysRegionalAgentDto;
import com.yungu.swift.system.sys.service.SysBusinessService;
import com.yungu.swift.utils.*;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.context.annotation.ScopedProxyMode;
import org.springframework.mock.web.MockMultipartFile;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.context.WebApplicationContext;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.net.URL;
import java.net.URLConnection;
import java.net.URLEncoder;
import java.util.*;
import java.util.concurrent.TimeUnit;
import java.util.zip.ZipEntry;
import java.util.zip.ZipInputStream;

import static com.yungu.swift.constants.CommonConstant.*;

import static com.yungu.swift.admin.config.NacosListener.WXPAY_CONFIG;

/**
 * @author : cuixiuyin
 * @date : 2019/6/26
 */
@Slf4j
@RestController
@RequestMapping("/admin/v1/operation/driver")
@Api(value = "【运营管理】", tags = "司机信息管理")
@Scope(value = WebApplicationContext.SCOPE_SESSION, proxyMode = ScopedProxyMode.TARGET_CLASS)
public class DriverAdmin {

    @Reference
    private DriverService driverService;
    @Reference
    private SysBusinessService sysBusinessService;
    @Autowired
    private RedisStringCacheService redisStringCacheService;

    /**
     * 错误的行数
     */
    private Set<Integer> errSet;

    /**
     * 导入模板
     */
    private Workbook workbook;

    /**
     * 导入模板名
     */
    private String fileName;

    @ResponseImgOverride
    @RequestMapping(value = "/queryPage", method = RequestMethod.POST)
    @ApiOperation(value = "司机信息管理分页查询", httpMethod = "POST")
    public ResponseData<PageVo<AdminDriverVo>> queryPage(@RequestBody AdminDriverPageParam driverPageParam) {
        return driverService.queryPageAdmin(driverPageParam);
    }

    @ResponseImgOverride
    @RequestMapping(value = "/detail", method = RequestMethod.GET)
    @ApiOperation(value = "司机信息详情", httpMethod = "GET")
    public ResponseData<DriverDto> detail(@RequestParam String uuid, @CurrentUser AdminParam adminParam) {
        Map<String, Object> paramMap = MapUtils.build(4);
        paramMap.put("uuid", uuid);
        paramMap.put("userRoleUuid", adminParam.getUserRoleUuid());
        ResponseData<List<DriverDto>> listExtend = driverService.listExtend(paramMap);
        if (listExtend.isSuccess() && CollectionUtils.isNotEmpty(listExtend.getData())) {
            return ResponseData.buildSuccessResponse(listExtend.getData().get(0));
        }
        return ResponseData.buildSuccessResponse(new DriverDto());
    }

    @RequestMapping(value = "/changeStatus", method = RequestMethod.POST)
    @ApiOperation(value = "司机封号/解封操作", httpMethod = "POST")
    public ResponseData<Boolean> changeStatus(@RequestBody AdminDriverAbortParam abortParam) {
        if (ParamUtil.isIllegal(abortParam.getUuid(), abortParam.getStatus(), abortParam.getAbortRemark())) {
            return ResponseData.buildErrorResponse(AdminErrorEnum.PARAM_ERROR.getCode(), AdminErrorEnum.PARAM_ERROR.getMsg());
        }
        // 解封时候，针对司机未激活状态做额外处理
        ResponseData<DriverDto> responseData = driverService.get(abortParam.getUuid());
        if (responseData.isSuccess() && responseData.getData() != null && DriverConstant.DRIVER_STATUS_NORMAL ==
                abortParam.getStatus()) {
            String requireColumn = checkRequireColumn(responseData.getData());
            if (StringUtils.isNotEmpty(requireColumn)) {
                abortParam.setStatus(DriverConstant.DRIVER_STATUS_INACTIVATED);
            }
            abortParam.setAbortTime(null);
            abortParam.setAbortRemark(null);
        }
        return driverService.changeStatus(abortParam);
    }

    @RequestMapping(value = "/dismiss", method = RequestMethod.POST)
    @ApiOperation(value = "司机离职操作", httpMethod = "POST")
    public ResponseData<Boolean> dismiss(@RequestBody AdminDriverDismissParam dismissParam) {
        if (ParamUtil.isIllegal(dismissParam.getUuid(), dismissParam.getDimissionRemark())) {
            return ResponseData.buildErrorResponse(AdminErrorEnum.PARAM_ERROR.getCode(), AdminErrorEnum.PARAM_ERROR.getMsg());
        }
        return driverService.driverDimission(dismissParam);
    }

    @ResponseImgOverride
    @RequestMapping(value = "/bindCarList", method = RequestMethod.POST)
    @ApiOperation(value = "模糊搜索该司机可绑定的车辆列表", httpMethod = "POST")
    public ResponseData<List<CarDto>> bindCarList(@RequestBody AdminDriverBindCarParam bindCarParam) {
        if (ParamUtil.isIllegal(bindCarParam.getUuid())) {
            return ResponseData.buildErrorResponse(AdminErrorEnum.PARAM_ERROR.getCode(), AdminErrorEnum.PARAM_ERROR.getMsg());
        }
        return driverService.bindCarList(bindCarParam);
    }

    @RequestMapping(value = "/bindCar", method = RequestMethod.POST)
    @ApiOperation(value = "司机绑定车辆", httpMethod = "POST")
    public ResponseData<Boolean> bindCar(@RequestBody AdminDriverBindCarParam bindCarParam) {
        if (ParamUtil.isIllegal(bindCarParam.getUuid(), bindCarParam.getPlateNum())) {
            return ResponseData.buildErrorResponse(AdminErrorEnum.PARAM_ERROR.getCode(), AdminErrorEnum.PARAM_ERROR.getMsg());
        }
        return driverService.bindCar(bindCarParam);
    }

    @RequestMapping(value = "/unBindCar", method = RequestMethod.POST)
    @ApiOperation(value = "司机解绑车辆", httpMethod = "POST")
    public ResponseData<Boolean> unBindCar(@RequestBody AdminDriverBindCarParam bindCarParam) {
        if (ParamUtil.isIllegal(bindCarParam.getUuid())) {
            return ResponseData.buildErrorResponse(AdminErrorEnum.PARAM_ERROR.getCode(), AdminErrorEnum.PARAM_ERROR.getMsg());
        }
        return driverService.unBindCar(bindCarParam);
    }


    @RequestMapping(value = "/saveOrUpdate", method = RequestMethod.POST)
    @ApiOperation(value = "司机信息新增或者更新", httpMethod = "POST")
    public ResponseData<Boolean> saveOrUpdate(@RequestBody AdminDriverParam driverParam) {
        if (ParamUtil.isIllegal(driverParam.getName(), driverParam.getIdCard(), driverParam.getMobile(),
                driverParam.getLicenseId(), driverParam.getDrivingLicenceHomeImg(), driverParam.getDrivingLicenceSubImg(),
                driverParam.getAgentUuid(), driverParam.getCompanyUuid(), driverParam.getIdCardFaceImg(),
                driverParam.getIdCardBackImg())) {
            return ResponseData.buildErrorResponse(AdminErrorEnum.PARAM_ERROR.getCode(), AdminErrorEnum.PARAM_ERROR.getMsg());
        }
        Integer businessType = driverParam.getBusinessType();
        if (businessType == BUSINESS_TYPE_TAXI) {
            if (ParamUtil.isIllegal(driverParam.getFace(), driverParam.getGetDriverLicenseDate(),
                    driverParam.getCertificateNo(), driverParam.getNetworkCarProofOff(),
                    driverParam.getCertificateImage())) {
                return ResponseData.buildErrorResponse(AdminErrorEnum.PARAM_ERROR.getCode(), AdminErrorEnum.PARAM_ERROR.getMsg());
            }
        }
        if (businessType == BUSINESS_TYPE_SPEC || businessType == BUSINESS_TYPE_EXPRESS) {
            if (ParamUtil.isIllegal(driverParam.getDriverIdCardImg())) {
                return ResponseData.buildErrorResponse(AdminErrorEnum.PARAM_ERROR.getCode(), AdminErrorEnum.PARAM_ERROR.getMsg());
            }
        }
        DriverDto driverDto = new DriverDto();
        BeanUtils.copyProperties(driverParam, driverDto);
        List<DriverTrainDto> trainDtoList = new ArrayList<>();
        //设置培训信息
        if (CollectionUtils.isNotEmpty(driverParam.getDriverTrainParams())) {
            for (AdminDriverParam.AdminDriverTrainParam trainParam : driverParam.getDriverTrainParams()) {
                DriverTrainDto driverTrainDto = new DriverTrainDto();
                BeanUtils.copyProperties(trainParam, driverTrainDto);
                trainDtoList.add(driverTrainDto);
            }
        }
        driverDto.setDriverTrainDtoList(trainDtoList);
        // 解密数据
        if (driverDto.getName() != null) {
            driverDto.setName(RandomCipherUtils.randomDecrypt(driverDto.getName()));
        }
        if (driverDto.getIdCard() != null) {
            driverDto.setIdCard(RandomCipherUtils.randomDecrypt(driverDto.getIdCard()));
        }
        if (driverDto.getMobile() != null) {
            driverDto.setMobile(RandomCipherUtils.randomDecrypt(driverDto.getMobile()));
        }
        if (driverDto.getDriverContactAddress() != null) {
            driverDto.setDriverContactAddress(RandomCipherUtils.randomDecrypt(driverDto.getDriverContactAddress()));
        }
        if (driverDto.getLicenseId() != null) {
            driverDto.setLicenseId(RandomCipherUtils.randomDecrypt(driverDto.getLicenseId()));
        }
        if (driverDto.getCertificateNo() != null) {
            driverDto.setCertificateNo(RandomCipherUtils.randomDecrypt(driverDto.getCertificateNo()));
        }
        log.info("司机银行账户信息1111{}", driverDto.getBankAccountName());
        if (driverDto.getBankAccountName() != null) {
            log.info("司机银行账户信息{}-{}", driverDto.getBankAccountName(), RandomCipherUtils.randomDecrypt(driverDto.getBankAccountName()));
            driverDto.setBankAccountName(RandomCipherUtils.randomDecrypt(driverDto.getBankAccountName()));
        }
        log.info("司机银行代码信息code{}", driverDto.getBankAccount());
        if (driverDto.getBankAccount() != null) {
            log.info("司机银行代码信息code22{}--{}", driverDto.getBankAccount(), RandomCipherUtils.randomDecrypt(driverDto.getBankAccount()));
            driverDto.setBankAccount(RandomCipherUtils.randomDecrypt(driverDto.getBankAccount()));
        }
        if (driverDto.getBankName() != null) {
            log.info("司机银行名称信息jjjjj{}--{}", driverDto.getBankName(), RandomCipherUtils.randomDecrypt(driverDto.getBankName()));
            driverDto.setBankName(RandomCipherUtils.randomDecrypt(driverDto.getBankName()));
        }
        if (driverDto.getBankAddress() != null) {
            driverDto.setBankAddress(RandomCipherUtils.randomDecrypt(driverDto.getBankAddress()));
        }
        if (driverDto.getDriverCensus() != null) {
            driverDto.setDriverCensus(RandomCipherUtils.randomDecrypt(driverDto.getDriverCensus()));
        }
        if (driverDto.getDriverAddress() != null) {
            driverDto.setDriverAddress(RandomCipherUtils.randomDecrypt(driverDto.getDriverAddress()));
        }
        if (driverDto.getEmergencyContact() != null) {
            driverDto.setEmergencyContact(RandomCipherUtils.randomDecrypt(driverDto.getEmergencyContact()));
        }
        if (driverDto.getEmergencyContactPhone() != null) {
            driverDto.setEmergencyContactPhone(RandomCipherUtils.randomDecrypt(driverDto.getEmergencyContactPhone()));
        }
        if (driverDto.getEmergencyContactAddress() != null) {
            driverDto.setEmergencyContactAddress(RandomCipherUtils.randomDecrypt(driverDto.getEmergencyContactAddress()));
        }
        if (driverDto.getPlateNum() != null) {
            driverDto.setPlateNum(RandomCipherUtils.randomDecrypt(driverDto.getPlateNum()));
        }
        String uuid = driverParam.getUuid();
        Map<String, Object> param = MapUtils.build(4);
        param.put("mobile", driverDto.getMobile());
        param.put("appid", driverDto.getAppid());
        //过滤司机离职
        param.put("dimissionStatus", DriverConstant.DRIVER_STATUS_DIMISSION);
        if (StringUtils.isNotEmpty(uuid)) {
            param.put("excludeUuid", uuid);
        }
        ResponseData<DriverDto> checkMobileRsd = driverService.get(param);
        if (checkMobileRsd.isSuccess() && checkMobileRsd.getData() != null) {
            return ResponseData.buildErrorResponse(ResponseData.ERROR_CODE, "该手机已被注册");
        }
        param.clear();
        param.put("idCard", driverParam.getIdCard());
        if (StringUtils.isNotEmpty(uuid)) {
            param.put("excludeUuid", uuid);
        }
        ResponseData<DriverDto> checkIdRsd = driverService.get(param);
        if (checkIdRsd.isSuccess() && checkMobileRsd.getData() != null) {
            return ResponseData.buildErrorResponse(ResponseData.ERROR_CODE, "该身份证号已被注册");
        }
        // 激活司机
        driverDto.setStatus(DriverConstant.DRIVER_STATUS_NORMAL);
        if (StringUtils.isEmpty(uuid)) {
            driverDto.setCreateBy(driverParam.getUserUuid());
            driverDto.setCreateOn(new Date());
            //默认国籍为中国
            driverDto.setDriverNationality("156");
            driverDto.setUuid(StringUtils.buildUUID());
            return driverService.add(driverDto);
        }
        driverDto.setUpdateBy(driverParam.getUserUuid());
        driverDto.setUpdateOn(new Date());
        return driverService.edit(driverDto);
    }


    @RequestMapping(value = "/downloadExcelTemplate", method = RequestMethod.GET)
    @ApiOperation(value = "下载导入Excel模板", httpMethod = "GET")
    public void downloadExcelTemplate(HttpServletResponse response, @RequestParam Integer businessType) throws IOException {
        InputStream in;
        String fileName;
        switch (businessType) {
            case 1:
                in = getClass().getResourceAsStream("/template/taxiDriverImport.xlsx");
                fileName = "出租车司机导入模板.xlsx";
                break;
            case 3:
                in = getClass().getResourceAsStream("/template/crossDrivrImport.xlsx");
                fileName = "拼车司机导入模板.xlsx";
                break;
            default:
                in = getClass().getResourceAsStream("/template/expressDriverImport.xlsx");
                fileName = "专快车司机导入模板.xlsx";
                break;
        }
        DownloadUtils.download(response, in, fileName);
    }

    @RequestMapping(value = "/import", method = RequestMethod.POST)
    @ApiOperation(value = "司机信息导入", httpMethod = "POST")
    @Transactional(rollbackFor = Exception.class)
    public ResponseData<Boolean> importDriver(@RequestPart(value = "file") MultipartFile file,
                                              @RequestParam(value = "type") Integer businessType,
                                              @CurrentUser AdminParam adminParam) {
        //初始化表格
        workbook = null;
        errSet = new HashSet<>();
        fileName = file.getOriginalFilename();
        try {
            StringBuilder errmsg = new StringBuilder();
            Map<String, DriverDto> driverDtoMap = new HashMap<>();
            Map<String, DriverDto> updatingDriverDtoMap = new HashMap<>();
            Map<String, DriverTrainDto> driverTrainDtoMap = new HashMap<>();
            Map<String, DriverTrainDto> updatingDriverTrainDtoMap = new HashMap<>();

            //代理：name-bean
            Map<String, SysRegionalAgentDto> agentMap = DataListHelper.getAgentName2BeanMap();
            //企业：name-bean
            Map<String, SysCompanyDto> companyMap = DataListHelper.getCompanyName2BeanMap();
            //车辆：plateNum-bean
            Map<String, CarDto> carTablePlateNum2BeanMap = DataListHelper.getCarPlateNum2BeanMap();
            //司机：mobile-bean
            Map<String, DriverDto> driverMobile2BeanMap = DataListHelper.getDriverMobile2BeanMap();
            //司机：idCard-bean
            Map<String, DriverDto> driverIdCard2BeanMap = DataListHelper.getDriverIdCard2BeanMap();
            //表中手机号集合
            Set<String> allMobileSet = new HashSet<>(driverMobile2BeanMap.keySet());
            //表中身份证号集合
            Set<String> allIdcardSet = new HashSet<>(driverIdCard2BeanMap.keySet());

            workbook = WorkbookFactory.create(file.getInputStream());
            Sheet sheet = workbook.getSheetAt(0);
            if (workbook.getNumberOfSheets() > 1) {
                sheet = this.workbook.getSheet("司机导入格式");
            }
            if (sheet == null) {
                return ResponseData.buildErrorResponse(ResponseData.ERROR_CODE, "文件数据缺失");
            }
            Row firstRow = sheet.getRow(0);
            if (firstRow == null) {
                return ResponseData.buildErrorResponse(ResponseData.ERROR_CODE, "文件数据缺失");
            }
            for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (row == null) {
                    continue;
                }
                DriverDto driverDto = new DriverDto();
                DriverTrainDto driverTrainDto = new DriverTrainDto();
                // 读取一行数据并保存到对象中
                DriverImportVo driverImportVo = readRowToBean(row, 0, businessType);
                // 格式校验 失败则继续读取下一行数据
                if (!validateRow(driverImportVo, row, 0, businessType)) {
                    errSet.add(rowNum);
                    continue;
                }
                // 业务校验
                // 校验身份证号
                if (allIdcardSet.contains(driverImportVo.getLicenseId())) {
                    errmsg.append(driverImportVo.getLicenseId() + "身份证号已存在").append("\r\n");
                    errSet.add(rowNum);
                    continue;
                }
                allIdcardSet.add(driverImportVo.getLicenseId());
                // 校验手机号
                if (allMobileSet.contains(driverImportVo.getMobile())) {
                    errmsg.append(driverImportVo.getMobile() + "手机号已存在").append("\r\n");
                    errSet.add(rowNum);
                    continue;
                }
                allMobileSet.add(driverImportVo.getMobile());
                // 校验代理
                String agent = driverImportVo.getAgent();
                if (!agentMap.containsKey(agent)) {
                    errmsg.append(agent + "代理不存在").append("\r\n");
                    errSet.add(rowNum);
                    continue;
                } else {
                    Map<String, List<SysBusinessDto>> listBusMap = DataListHelper.getOrgUuid_ListBusMap(adminParam.getAppid());
                    List<SysBusinessDto> sysBusinessDtos = listBusMap.get(agentMap.get(agent).getUuid());
                    SysBusinessDto businessDto = sysBusinessDtos.stream().filter(sysBusinessDto -> businessType.equals(sysBusinessDto.getType())).findAny().orElse(null);
                    if (businessDto == null) {
                        errmsg.append("代理没有开通该业务").append("\r\n");
                        errSet.add(rowNum);
                        continue;
                    }
                }
                driverImportVo.setAgent(agentMap.get(agent).getUuid());
                // 检验公司
                String company = driverImportVo.getCompany();
                if (!companyMap.containsKey(company)) {
                    errmsg.append(company + "公司不存在").append("\r\n");
                    errSet.add(rowNum);
                    continue;
                } else {
                    SysCompanyDto sysCompanyDto = companyMap.get(company);
                    if (!sysCompanyDto.getRegionalAgentUuid().equals(driverImportVo.getAgent())) {
                        errmsg.append(company + "和代理不匹配").append("\r\n");
                        errSet.add(rowNum);
                        continue;
                    }
                    Map<String, List<SysBusinessDto>> listBusMap = DataListHelper.getOrgUuid_ListBusMap(adminParam.getAppid());
                    List<SysBusinessDto> sysBusinessDtos = listBusMap.get(companyMap.get(company).getUuid());
                    SysBusinessDto businessDto = sysBusinessDtos.stream().filter(sysBusinessDto -> businessType.equals(sysBusinessDto.getType())).findAny().orElse(null);
                    if (businessDto == null) {
                        errmsg.append("公司没有开通该业务").append("\r\n");
                        errSet.add(rowNum);
                        continue;
                    }
                }
                driverImportVo.setCompany(companyMap.get(company).getUuid());

                // 校验车牌号
                String plateNum = driverImportVo.getPlateNum();
                if (StringUtils.isEmpty(plateNum)) {
                    driverImportVo.setPlateNum("");
                }
                CarDto carDto = carTablePlateNum2BeanMap.get(plateNum);
                if (carDto == null) {
                    driverImportVo.setPlateNum("");
                } else {
                    if (!carDto.getAgentUuid().equals(driverImportVo.getAgent()) || !carDto.getCompanyUuid().equals(
                            driverImportVo.getCompany()) || !carDto.getBusinessType().equals(businessType)) {
                        errmsg.append(plateNum + "车牌号与司机相关业务不符合").append("\r\n");
                        errSet.add(rowNum);
                        driverImportVo.setPlateNum("");
                    }
                    driverImportVo.setPlateNum(carDto.getUuid());
                }
                //针对司机身份证号、手机号唯一性检验
                // 1 新增 2 更新
                int flag = 0;
                // 身份证号被注册
                if (driverIdCard2BeanMap.containsKey(driverImportVo.getLicenseId())) {
                    String driverMobile = driverIdCard2BeanMap.get(driverImportVo.getLicenseId()).getMobile();
                    // 身份证号注册的司机绑定的手机号与导入的一致。更新
                    if (driverMobile.equals(driverImportVo.getMobile())) {
                        flag = 2;
                    }
                    // 身份证号注册的司机手机号信息与导入的不一致，且手机号可用。更新
                    if (!driverMobile.equals(driverImportVo.getMobile()) && !driverMobile2BeanMap.containsKey(driverImportVo.getMobile())) {
                        flag = 2;
                    }
                    // 身份证号注册的司机手机号信息与导入的不一致，且手机号不可用。更新
                    if (!driverMobile.equals(driverImportVo.getMobile()) && driverMobile2BeanMap.containsKey(driverImportVo.getMobile())) {
                        errmsg.append(driverImportVo.getMobile()).append("手机号已经被其他身份证号绑定了").append("\r\n");
                        errSet.add(rowNum);
                        continue;
                    }
                }
                // 身份证号未注册
                if (!driverIdCard2BeanMap.containsKey(driverImportVo.getLicenseId())) {
                    // 手机号已经被其他身份证号绑定了。校验失败
                    if (driverMobile2BeanMap.containsKey(driverImportVo.getMobile())) {
                        errmsg.append(driverImportVo.getMobile()).append("手机号已经被其他身份证号绑定了").append("\r\n");
                        errSet.add(rowNum);
                        continue;
                    }
                    flag = 1;
                }
                driverDto.setBusinessType(businessType);
                driverDto.setCompanyUuid(driverImportVo.getCompany());
                driverDto.setAgentUuid(driverImportVo.getAgent());
                driverDto.setDriverNo(driverImportVo.getDriverNo());
                driverDto.setName(driverImportVo.getName());
                driverDto.setMobile(driverImportVo.getMobile());
                // 将char转成对应的int 非ASCII码
                if (StringUtils.isNotEmpty(driverImportVo.getSex())) {
                    driverDto.setSex(driverImportVo.getSex().charAt(0) - '0');
                }
                // 默认中国
                driverDto.setDriverNationality(156 + "");
                // 民族（过滤中文）
                driverDto.setDriverNation(driverImportVo.getDriverNation().replaceAll("[\\u4e00-\\u9faf]", ""));
                // 驾驶员婚姻状况10.未婚，20已婚，40离婚
                driverDto.setDriverMaritalStatus(Integer.parseInt(driverImportVo.getDriverMaritalStatus().replaceAll("[\\u4e00-\\u9faf]", "")));
                driverDto.setDriverLanguageLevel(Integer.parseInt(driverImportVo.getDriverLanguageLevel().replaceAll("[\\u4e00-\\u9faf]", "")));
                driverDto.setDriverEducation(Integer.parseInt(driverImportVo.getDriverEducation().replaceAll("[\\u4e00-\\u9faf]", "")));
                driverDto.setDriverCensus(driverImportVo.getDriverCensus());
                driverDto.setDriverAddress(driverImportVo.getDriverAddress());
                driverDto.setDriverContactAddress(driverImportVo.getDriverContactAddress());
                driverDto.setLicenseId(driverImportVo.getLicenseId());
                // 身份证号同驾驶证号
                driverDto.setIdCard(driverImportVo.getLicenseId());
                driverDto.setDriverType(driverImportVo.getDriverType());
                driverDto.setGetDriverLicenseDate(DateUtils.parse(driverImportVo.getGetDriverLicenseDate(), "yyyy-MM-dd"));
                driverDto.setDriverLicenseOn(DateUtils.parse(driverImportVo.getDriverLicenseOn(), "yyyy-MM-dd"));
                driverDto.setDriverLicenseOff(DateUtils.parse(driverImportVo.getDriverLicenseOff(), "yyyy-MM-dd"));
                if (StringUtils.isNotEmpty(driverImportVo.getTaxiDriver())) {
                    driverDto.setTaxiDriver(Integer.valueOf(driverImportVo.getTaxiDriver().charAt(0) - '0'));
                }
                driverDto.setCertificateNo(driverImportVo.getCertificateNo());
                driverDto.setNetworkCarIssueOrganization(driverImportVo.getNetworkCarIssueOrganization());
                driverDto.setNetworkCarIssueDate(DateUtils.parse(driverImportVo.getNetworkCarIssueDate(), "yyyy-MM-dd"));
                driverDto.setGetNetworkCarProofDate(DateUtils.parse(driverImportVo.getGetNetworkCarProofDate(), "yyyy-MM-dd"));
                driverDto.setNetworkCarProofOn(DateUtils.parse(driverImportVo.getNetworkCarProofOn(), "yyyy-MM-dd"));
                driverDto.setNetworkCarProofOff(DateUtils.parse(driverImportVo.getNetworkCarProofOff(), "yyyy-MM-dd"));
                driverDto.setRegisterDate(DateUtils.parse(driverImportVo.getRegisterDate(), "yyyy-MM-dd"));
                if (StringUtils.isNotEmpty(driverImportVo.getFullTimeDriver())) {
                    driverDto.setFullTimeDriver(Integer.valueOf(driverImportVo.getFullTimeDriver().charAt(0) - '0'));
                }
                if (StringUtils.isNotEmpty(driverImportVo.getInDriverBlacklist())) {
                    driverDto.setInDriverBlacklist(Integer.valueOf(driverImportVo.getInDriverBlacklist().charAt(0) - '0'));
                }
                if (StringUtils.isNotEmpty(driverImportVo.getCommercialType())) {
                    driverDto.setCommercialType(Integer.valueOf(driverImportVo.getCommercialType().charAt(0) - '0'));
                }
                if (StringUtils.isNotEmpty(driverImportVo.getContractType())) {
                    driverDto.setContractType(StringUtils.left(driverImportVo.getContractType(), 2));
                }
                driverDto.setContractCompany(driverImportVo.getContractCompany());
                driverDto.setContractSign(DateUtils.parse(driverImportVo.getContractSign(), "yyyy-MM-dd"));
                driverDto.setContractOn(DateUtils.parse(driverImportVo.getContractOn(), "yyyy-MM-dd"));
                driverDto.setContractOff(DateUtils.parse(driverImportVo.getContractOff(), "yyyy-MM-dd"));
                driverDto.setEmergencyContact(driverImportVo.getEmergencyContact());
                driverDto.setEmergencyContactPhone(driverImportVo.getEmergencyContactPhone());
                driverDto.setEmergencyContactAddress(driverImportVo.getEmergencyContactAddress());
                driverDto.setState(MagicConstant.INT_ONE);
                if (StringUtils.isNotEmpty(driverImportVo.getTrafficViolationsCount())) {
                    driverDto.setTrafficViolationsCount(Integer.valueOf(driverImportVo.getTrafficViolationsCount()));
                }
                if (StringUtils.isNotEmpty(driverImportVo.getTrafficAccidentCount())) {
                    driverDto.setTrafficAccidentCount(Integer.valueOf(driverImportVo.getTrafficAccidentCount()));
                }
                driverDto.setCertificateNo(driverImportVo.getCertificateNo());
                driverDto.setBankName(driverImportVo.getBankName());
                driverDto.setBankAccountName(driverImportVo.getBankAccountName());
                driverDto.setBankAccount(driverImportVo.getBankAccount());
                driverDto.setBankAddress(driverImportVo.getBankAddress());
                driverDto.setCarUuid(driverImportVo.getPlateNum());
                /**
                 * 培训子表
                 */
                driverTrainDto.setCourseName(driverImportVo.getCourseName());
                driverTrainDto.setCourseDate(DateUtils.parse(driverImportVo.getCourseDate(), "yyyy-MM-dd"));
                driverTrainDto.setStartTime(DateUtils.parse(driverImportVo.getStartTime(), "yyyy-MM-dd"));
                driverTrainDto.setStopTime(DateUtils.parse(driverImportVo.getStopTime(), "yyyy-MM-dd"));
                if (StringUtils.isNotEmpty(driverImportVo.getDuration())) {
                    driverTrainDto.setDuration(BigDecimal.valueOf(Double.parseDouble(driverImportVo.getDuration())));
                }
                //新增
                if (flag == 1) {
                    driverDto.setUuid(StringUtils.buildUUID());
                    driverDto.setPassword(Md5Utils.encode("123456"));
                    driverDto.setIsWork(1);
                    driverDto.setStatus(DriverConstant.DRIVER_STATUS_INACTIVATED);
                    driverDto.setIsFirst(1);
                    driverDto.setPushLevel(1);
                    // 默认设置听单模式为全部
                    driverDto.setRemindType(1);
                    // 设置余额为0
                    driverDto.setBalance(0D);
                    driverDto.setOrderCount(0);
                    driverDto.setCreateOn(new Date());
                    driverDto.setCreateBy(adminParam.getUserUuid());
                    driverDto.setAppid(adminParam.getAppid());
                    driverDtoMap.put(driverDto.getUuid(), driverDto);

                    driverTrainDto.setUuid(StringUtils.buildUUID());
                    // 岗前培训
                    driverTrainDto.setType(MagicConstant.INT_ONE);
                    driverTrainDto.setLicenseId(driverDto.getIdCard());
                    driverTrainDto.setDriverUuid(driverDto.getUuid());
                    driverTrainDto.setCreateOn(driverDto.getCreateOn());
                    driverTrainDto.setCreateBy(adminParam.getUserUuid());
                    driverTrainDtoMap.put(driverTrainDto.getUuid(), driverTrainDto);
                }
                if (flag == 2) {
                    //更新
                    driverDto.setUpdateBy(adminParam.getUserUuid());
                    driverDto.setUpdateOn(new Date());
                    driverDto.setUuid(driverIdCard2BeanMap.get(driverImportVo.getLicenseId()).getUuid());
                    updatingDriverDtoMap.put(driverDto.getUuid(), driverDto);

                    driverTrainDto.setUpdateBy(adminParam.getUserUuid());
                    driverTrainDto.setUpdateOn(driverDto.getUpdateOn());
                    driverTrainDto.setDriverUuid(driverDto.getUuid());
                    updatingDriverTrainDtoMap.put(driverTrainDto.getUuid(), driverTrainDto);
                }
            }

            // 所有待插入的司机集合
            List<DriverDto> driverDtoList = new ArrayList<>(driverDtoMap.values());
            // 所有待更新的司机集合
            List<DriverDto> updatingDriverDtoList = new ArrayList<>(updatingDriverDtoMap.values());
            // 所有待插入的司机培训集合
            List<DriverTrainDto> driverTrainDtoList = new ArrayList<>(driverTrainDtoMap.values());
            // 所有待更新的司机培训集合
            List<DriverTrainDto> updatingDriverTrainDtoList = new ArrayList<>(updatingDriverTrainDtoMap.values());

            //保存数据
            driverService.importDriver(driverDtoList, driverTrainDtoList, updatingDriverDtoList, updatingDriverTrainDtoList);

            //返回结果
            if (errSet.size() == 0 && StringUtils.isEmpty(errmsg.toString())) {
                return ResponseData.buildSuccessResponse("信息导入成功 ，继续导入图片后，司机即可开通成功", Boolean.TRUE);
            }
            return ResponseData.buildErrorResponse(ResponseData.ERROR_CODE, StringUtils.isEmpty(errmsg.toString()) ?
                    "信息填写有误，请点击下载导入结果，修改后再次导入" : errmsg.toString());
        } catch (Exception e) {
            e.printStackTrace();
            errSet = new HashSet<>();
        }
        return ResponseData.buildErrorResponse(ResponseData.ERROR_CODE, "系统异常，请联系管理员解决");
    }

    /**
     * 读取 Excel 表格的一行数据
     */
    private DriverImportVo readRowToBean(Row row, int startColNum, int type) {
        DriverImportVo driverImportVo = new DriverImportVo();
        //取值
        // 司机分组
        driverImportVo.setDriverNo(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 机动车驾驶员姓名
        driverImportVo.setName(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 驾驶员手机号
        driverImportVo.setMobile(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 驾驶员性别
        driverImportVo.setSex(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 驾驶员民族
        driverImportVo.setDriverNation(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 驾驶员婚姻状况
        driverImportVo.setDriverMaritalStatus(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 驾驶员外语能力
        driverImportVo.setDriverLanguageLevel(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 驾驶员学历
        driverImportVo.setDriverEducation(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 户口登记机关名称
        driverImportVo.setDriverCensus(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 户口住址或长住地址
        driverImportVo.setDriverAddress(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 驾驶员通信地址
        driverImportVo.setDriverContactAddress(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 机动车驾驶证号
        driverImportVo.setLicenseId(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 准驾车型
        driverImportVo.setDriverType(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 初次领取驾驶证日期
        driverImportVo.setGetDriverLicenseDate(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 驾驶证有效期限起
        driverImportVo.setDriverLicenseOn(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 驾驶证有效期限止
        driverImportVo.setDriverLicenseOff(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 是否巡游出租汽车驾驶员
        driverImportVo.setTaxiDriver(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 网络预约出租汽车驾驶员资格证号
        driverImportVo.setCertificateNo(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 网络预约出租汽车驾驶员证发证机构
        driverImportVo.setNetworkCarIssueOrganization(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 资格证发证日期
        driverImportVo.setNetworkCarIssueDate(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 初次领取资格证日期
        driverImportVo.setGetNetworkCarProofDate(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 资格证有效起始日期
        driverImportVo.setNetworkCarProofOn(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 资格证有效截止日期
        driverImportVo.setNetworkCarProofOff(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 报备日期
        driverImportVo.setRegisterDate(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 是否专职驾驶员
        driverImportVo.setFullTimeDriver(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 是否在驾驶员黑名单内
        driverImportVo.setInDriverBlacklist(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 服务类型
        driverImportVo.setCommercialType(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 驾驶员合同类型
        driverImportVo.setContractType(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 驾驶员合同（或协议）签署公司
        driverImportVo.setContractCompany(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 驾驶员合同签订日期
        driverImportVo.setContractSign(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 合同（或协议）有效期起
        driverImportVo.setContractOn(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 合同（或协议）有效期止
        driverImportVo.setContractOff(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 紧急情况联系人
        driverImportVo.setEmergencyContact(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 紧急情况联系人电话
        driverImportVo.setEmergencyContactPhone(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 紧急情况联系人通信地址
        driverImportVo.setEmergencyContactAddress(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 驾驶员培训课程名称
        driverImportVo.setCourseName(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 培训课程日期
        driverImportVo.setCourseDate(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 培训开始时间
        driverImportVo.setStartTime(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 培训结束时间
        driverImportVo.setStopTime(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 培训时长
        driverImportVo.setDuration(WorkbookUtil.getCellValue(row.getCell(startColNum++)));

        // 交通违章次数
        driverImportVo.setTrafficViolationsCount(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 交通事故次数
        driverImportVo.setTrafficAccidentCount(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 银行名称
        driverImportVo.setBankName(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 银行账户名称
        driverImportVo.setBankAccountName(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 银行卡号
        driverImportVo.setBankAccount(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 银行开户地址
        driverImportVo.setBankAddress(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 所属代理
        driverImportVo.setAgent(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 所属企业
        driverImportVo.setCompany(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        // 车牌号
        driverImportVo.setPlateNum(WorkbookUtil.getCellValue(row.getCell(startColNum++)));
        return driverImportVo;
    }

    /**
     * 校验传入的每一行的数据
     */
    private boolean validateRow(DriverImportVo driverImportVo, Row row, int startColNum, int type) {
        Boolean flag = true;
        CellStyle cellStyle = this.workbook.createCellStyle();
        cellStyle.setFillForegroundColor(HSSFColor.YELLOW.index);
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        cellStyle.setFillBackgroundColor(HSSFColor.YELLOW.index);
        //备注：字段长度检验根据表结构来设置的。
        // 0：司机分组 —— 0-10位的数字或者英文组合
        String driverNo = driverImportVo.getDriverNo();
        if (StringUtils.isNotEmpty(driverNo) && !driverNo.matches("^[A-Za-z0-9]{0,10}$")) {
            row.getCell(0 + startColNum).setCellStyle(cellStyle);
            flag = false;
        }
        // 1：机动车驾驶员姓名
        String beanName = driverImportVo.getName();
        if (StringUtils.isEmpty(beanName) || beanName.length() > 100) {
            setCellStyle(row, row.getCell(1 + startColNum), 1 + startColNum, cellStyle);
            flag = false;
        }
        // 2：驾驶员手机号
        String beanMobile = driverImportVo.getMobile();
        if (StringUtils.isEmpty(beanMobile) || !ValidateUtils.validateMobile(beanMobile)) {
            setCellStyle(row, row.getCell(2 + startColNum), 2 + startColNum, cellStyle);
            flag = false;
        }
        // 3：驾驶员性别 略
        // 4：驾驶员民族 略
        // 5：驾驶员婚姻状况 略
        // 6：驾驶员外语能力 略
        // 7：驾驶员学历 略
        // 8：户口登记机关名称
        String beanDriverCensus = driverImportVo.getDriverCensus();
        if (!StringUtils.isEmpty(beanDriverCensus) && beanDriverCensus.length() > 255) {
            row.getCell(8 + startColNum).setCellStyle(cellStyle);
            flag = false;
        }
        // 9：户口住址或长住地址
        String beanDriverAddress = driverImportVo.getDriverAddress();
        if (!StringUtils.isEmpty(beanDriverAddress) && beanDriverAddress.length() > 255) {
            row.getCell(9 + startColNum).setCellStyle(cellStyle);
            flag = false;

        }
        //10：驾驶员通信地址
        String beanDriverContactAddress = driverImportVo.getDriverContactAddress();
        if (!StringUtils.isEmpty(beanDriverContactAddress) && beanDriverContactAddress.length() > 255) {
            row.getCell(10 + startColNum).setCellStyle(cellStyle);
            flag = false;
        }
        // 11：驾驶证号
        String licenseId = driverImportVo.getLicenseId();
        if (StringUtils.isEmpty(licenseId) || !ValidateUtils.checkIdCard(licenseId)) {
            setCellStyle(row, row.getCell(11 + startColNum), 12 + startColNum, cellStyle);
            flag = false;
        }
        // 12：准驾车型 略
        // 13：初次领取驾驶证日期
        String getDriverLicenseDate = driverImportVo.getGetDriverLicenseDate();
        if (StringUtils.isNotEmpty(getDriverLicenseDate) && !WorkbookUtil.checkDataFormat(getDriverLicenseDate)) {
            row.getCell(13 + startColNum).setCellStyle(cellStyle);
            flag = false;
        }
        // 14：驾驶证有效期限起
        String driverLicenseOn = driverImportVo.getDriverLicenseOn();
        if (StringUtils.isNotEmpty(driverLicenseOn) && !WorkbookUtil.checkDataFormat(driverLicenseOn)) {
            row.getCell(14 + startColNum).setCellStyle(cellStyle);
            flag = false;
        }
        // 15：驾驶证有效期限止
        String driverLicenseOff = driverImportVo.getDriverLicenseOff();
        if (StringUtils.isNotEmpty(driverLicenseOff) && !WorkbookUtil.checkDataFormat(driverLicenseOff)) {
            row.getCell(15 + startColNum).setCellStyle(cellStyle);
            flag = false;
        }
        // 16：是否巡游出租汽车驾驶员 略
        // 17：网络预约出租汽车驾驶员资格证号
        String beanCertificateNo = driverImportVo.getCertificateNo();
        if (StringUtils.isNotEmpty(beanCertificateNo) && !beanCertificateNo.matches("^[a-zA-Z0-9]{1}[a-zA-Z0-9]{17,18}$")) {
            setCellStyle(row, row.getCell(17 + startColNum), 18 + startColNum, cellStyle);
            flag = false;
        }
        // 18：网络预约出租汽车驾驶员证发证机构
        String beanNetworkCarIssueOrganization = driverImportVo.getNetworkCarIssueOrganization();
        if (!StringUtils.isEmpty(beanNetworkCarIssueOrganization) && beanNetworkCarIssueOrganization.length() > 255) {
            row.getCell(18 + startColNum).setCellStyle(cellStyle);
            flag = false;
        }
        //:19：资格证发证日期
        String networkCarIssueDate = driverImportVo.getNetworkCarIssueDate();
        if (StringUtils.isNotEmpty(networkCarIssueDate) && !WorkbookUtil.checkDataFormat(networkCarIssueDate)) {
            row.getCell(19 + startColNum).setCellStyle(cellStyle);
            flag = false;
        }
        // 20：初次领取资格证日期
        String getNetworkCarProofDate = driverImportVo.getGetNetworkCarProofDate();
        if (StringUtils.isNotEmpty(getNetworkCarProofDate) && !WorkbookUtil.checkDataFormat(getNetworkCarProofDate)) {
            row.getCell(20 + startColNum).setCellStyle(cellStyle);
            flag = false;
        }
        // 21：资格证有效起始日期
        String networkCarProofOn = driverImportVo.getNetworkCarProofOn();
        if (StringUtils.isNotEmpty(networkCarProofOn) && !WorkbookUtil.checkDataFormat(networkCarProofOn)) {
            row.getCell(21 + startColNum).setCellStyle(cellStyle);
            flag = false;
        }
        // 22：资格证有效截止日期
        String networkCarProofOff = driverImportVo.getNetworkCarProofOff();
        if (StringUtils.isNotEmpty(networkCarProofOff) && !WorkbookUtil.checkDataFormat(networkCarProofOff)) {
            setCellStyle(row, row.getCell(22 + startColNum), 23 + startColNum, cellStyle);
            flag = false;
        }
        // 23：报备日期
        String registerDate = driverImportVo.getRegisterDate();
        if (StringUtils.isNotEmpty(registerDate) && !WorkbookUtil.checkDataFormat(registerDate)) {
            row.getCell(23 + startColNum).setCellStyle(cellStyle);
            flag = false;
        }
        // 24：是否专职驾驶员 略
        // 25：是否在驾驶员黑名单 略
        // 26：服务类型 略
        // 27：驾驶员合同类型
        // 28：驾驶员合同签署公司
        String beanContractCompany = driverImportVo.getContractCompany();
        if (!StringUtils.isEmpty(beanContractCompany) && beanContractCompany.length() > 255) {
            row.getCell(28 + startColNum).setCellStyle(cellStyle);
            flag = false;
        }
        // 29：驾驶员合同签订日期
        String contractSign = driverImportVo.getContractSign();
        if (StringUtils.isNotEmpty(contractSign) && !WorkbookUtil.checkDataFormat(contractSign)) {
            row.getCell(29 + startColNum).setCellStyle(cellStyle);
            flag = false;
        }
        // 30：合同（或协议）有效期起
        String contractOn = driverImportVo.getContractOn();
        if (StringUtils.isNotEmpty(contractOn) && !WorkbookUtil.checkDataFormat(contractOn)) {
            row.getCell(30 + startColNum).setCellStyle(cellStyle);
            flag = false;
        }
        // 31：合同（或协议）有效期止
        String contractOff = driverImportVo.getContractOff();
        if (StringUtils.isNotEmpty(contractOff) && !WorkbookUtil.checkDataFormat(contractOff)) {
            row.getCell(31 + startColNum).setCellStyle(cellStyle);
            flag = false;
        }
        // 32：紧急情况联系人
        String beanEmergencyContact = driverImportVo.getEmergencyContact();
        if (!StringUtils.isEmpty(beanEmergencyContact) && beanEmergencyContact.length() > 255) {
            row.getCell(32 + startColNum).setCellStyle(cellStyle);
            flag = false;
        }
        // 33：紧急情况联系人电话
        String emergencyContactPhone = driverImportVo.getEmergencyContactPhone();
        if (!StringUtils.isEmpty(emergencyContactPhone) && !ValidateUtils.weakCheckMobile(emergencyContactPhone)) {
            row.getCell(33 + startColNum).setCellStyle(cellStyle);
            flag = false;
        }
        // 34：紧急情况联系人通信地址
        String emergencyContactAddress = driverImportVo.getEmergencyContactAddress();
        if (!StringUtils.isEmpty(driverImportVo.getEmergencyContactAddress()) && emergencyContactAddress.length() > 255) {
            row.getCell(34 + startColNum).setCellStyle(cellStyle);
        }
        // 35：驾驶员培训课程名称
        String beanCourseName = driverImportVo.getCourseName();
        if (!StringUtils.isEmpty(beanCourseName) && beanCourseName.length() > 64) {
            row.getCell(35 + startColNum).setCellStyle(cellStyle);
            flag = false;
        }
        // 36：培训课程日期
        String courseDate = driverImportVo.getCourseDate();
        if (StringUtils.isNotEmpty(courseDate) && !WorkbookUtil.checkDataFormat(courseDate)) {
            row.getCell(36 + startColNum).setCellStyle(cellStyle);
            flag = false;
        }
        // 37：培训开始时间
        String startTime = driverImportVo.getStartTime();
        if (StringUtils.isNotEmpty(startTime) && !WorkbookUtil.checkDataFormat(startTime)) {
            row.getCell(37 + startColNum).setCellStyle(cellStyle);
            flag = false;
        }
        // 38：培训结束时间
        String stopTime = driverImportVo.getStopTime();
        if (StringUtils.isNotEmpty(stopTime) && !WorkbookUtil.checkDataFormat(stopTime)) {
            row.getCell(38 + startColNum).setCellStyle(cellStyle);
            flag = false;
        }
        //:39：培训时长
        String beanDuration = driverImportVo.getDuration();
        if (!StringUtils.isEmpty(beanDuration) && beanDuration.length() > 9) {
            row.getCell(39 + startColNum).setCellStyle(cellStyle);
            flag = false;
        }
        // 40：交通违章次数
        // 41: 交通事故次数
        // 42：银行名称
        // 43：银行账户户名
        // 44：银行卡号
        String bankAccount = driverImportVo.getBankAccount();
        if (StringUtils.isNotEmpty(bankAccount) && ValidateUtils.checkBankCard(bankAccount)) {
            row.getCell(44 + startColNum).setCellStyle(cellStyle);
            flag = false;
        }
        // 45：银行开户行地址
        // 46：所属代理
        String beanAgent = driverImportVo.getAgent();
        if (StringUtils.isEmpty(beanAgent)) {
            setCellStyle(row, row.getCell(46 + startColNum), 41 + startColNum, cellStyle);
            flag = false;
        }
        // 47：所属公司
        if (StringUtils.isEmpty(driverImportVo.getCompany())) {
            setCellStyle(row, row.getCell(47 + startColNum), 42 + startColNum, cellStyle);
            flag = false;
        }
        // 43：所属业务
        // 出租车业务校验必填字段
        if (type == BUSINESS_TYPE_TAXI) {
            // 驾驶证初次领证日期
            if (StringUtils.isEmpty(driverImportVo.getGetDriverLicenseDate())) {
                row.getCell(13 + startColNum).setCellStyle(cellStyle);
                flag = false;
            }
            // 资格证号
            if (StringUtils.isEmpty(driverImportVo.getCertificateNo())) {
                setCellStyle(row, row.getCell(17 + startColNum), 18 + startColNum, cellStyle);
                flag = false;
            }
            // 资格证有效截止日期
            if (StringUtils.isEmpty(driverImportVo.getNetworkCarProofOff())) {
                setCellStyle(row, row.getCell(22 + startColNum), 23 + startColNum, cellStyle);
                flag = false;
            }
        }
        // 48：车牌号 略
        return flag;
    }

    private void setCellStyle(Row row, Cell cell, Integer cellnum, CellStyle cellStyle) {
        if (cell == null) {
            row.createCell(cellnum).setCellStyle(cellStyle);
        } else {
            cell.setCellStyle(cellStyle);
        }
    }


    @RequestMapping(value = "/downloadErrorExcel", method = RequestMethod.GET)
    @ApiOperation(value = "下载导入错误的 Excel 表格")
    public void downloadErrorExcel(HttpServletResponse response) throws IOException {
        try {
            Sheet sheet;
            int sheetCount = this.workbook.getNumberOfSheets();
            if (sheetCount > 1) {
                sheet = this.workbook.getSheet("司机导入格式");
            } else {
                sheet = this.workbook.getSheetAt(0);
            }
            int rowNum = sheet.getLastRowNum();
            for (int i = 1; i <= rowNum; i++) {
                if (errSet.contains(i)) {
                    continue;
                } else {
                    sheet.removeRow(sheet.getRow(i));
                }
            }
            // 删除空白行
            while (sheet.getLastRowNum() != errSet.size()) {
                removeNullRow(sheet);
            }
            OutputStream os = response.getOutputStream();
            response.setHeader("Content-Disposition", "attachment; filename=" + java.net.URLEncoder.encode(fileName, "UTF-8"));
            response.setContentType("application/octet-stream");
            response.setCharacterEncoding("utf-8");
            workbook.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private Sheet removeNullRow(Sheet sheet) {
        int lastRowNum = sheet.getLastRowNum();
        for (int i = 0; i < lastRowNum + 1; i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                sheet.shiftRows(i + 1, lastRowNum, -1);
                break;
            }
        }
        return sheet;
    }


    @RequestMapping(value = "/downloadZipTemplate", method = RequestMethod.GET)
    @ApiOperation(value = "下载图片导入模板", httpMethod = "GET")
    public void downloadZipTemplate(HttpServletResponse response) {
        DownloadUtils.download(response, getClass().getResourceAsStream("template/350825199001239999.zip"), "350825199001239999.zip");
    }

    @RequestMapping(value = "/uploadPictures", method = RequestMethod.POST)
    @ApiOperation(value = "司机信息图片导入", httpMethod = "POST")
    public ResponseData<Boolean> uploadPictures(@RequestPart("pictures") MultipartFile pictures,
                                                @RequestParam(required = false) String driverUuid,
                                                @RequestParam(required = false) Integer businessType,
                                                @CurrentUser AdminParam adminParam) throws IOException {
        StringBuilder errmsg = new StringBuilder(16);
        final String idCard = StringUtils.substringBefore(pictures.getOriginalFilename(), ".");
        // 用于单独的司机图片上传
        if (StringUtils.isNotEmpty(driverUuid)) {
            DriverDto dto = driverService.get(driverUuid).getData();
            if (!idCard.equalsIgnoreCase(dto.getIdCard())) {
                errmsg.append("此压缩包命名与该司机不符：" + idCard).append("\r\n");
                return ResponseData.buildErrorResponse(ResponseData.ERROR_CODE, errmsg.toString());
            }
        }
        // 判断业务
        DriverDto driverDto = driverService.getDriverByIdCard(idCard).getData();
        if (driverDto == null || !driverDto.getBusinessType().equals(businessType)) {
            errmsg.append("该业务下未发现该司机：" + idCard).append("\r\n");
            return ResponseData.buildErrorResponse(ResponseData.ERROR_CODE, errmsg.toString());
        }
        ZipInputStream zipInputStream = new ZipInputStream(pictures.getInputStream());
        BufferedInputStream bufferedInputStream = new BufferedInputStream(zipInputStream);
        ZipEntry entry;
        byte[] file;
        while ((entry = zipInputStream.getNextEntry()) != null) {
            String pictureName = StringUtils.substringAfter(entry.getName(), "/");
            if (StringUtils.isNotEmpty(pictureName)) {
                String prefix = StringUtils.left(pictureName, 18);
                if (!prefix.equalsIgnoreCase(idCard)) {
                    errmsg.append("图片命名与身份证不符：" + pictureName).append("\r\n");
                    continue;
                }
                file = new byte[(int) entry.getSize()];
                bufferedInputStream.read(file, 0, (int) entry.getSize());
                MultipartFile image = new MockMultipartFile("file", pictureName, "text/plain", file);
                // 上传图片
                String suffix = StringUtils.substringAfter(pictureName, ".");
                pictureName = StringUtils.substringBefore(pictureName, ".");
                ResponseData<String> ajaxList;
                String uploadPath;

                if ("pdf".equalsIgnoreCase(suffix)) {
                    ajaxList = UploadUtils.uploadFile(image, String.valueOf(adminParam.getAppid()), driverDto.getMobile());
                } else {
                    ajaxList = UploadUtils.uploadImage(image, String.valueOf(adminParam.getAppid()), driverDto.getMobile(), suffix);
                }
                if (!ajaxList.isSuccess()) {
                    return ResponseData.buildErrorResponse(ResponseData.ERROR_CODE, ajaxList.getMsg());
                }
                uploadPath = ajaxList.getData();
                // 设置图片路径
                if (pictureName.equals(idCard + "tx")) {
                    driverDto.setFace(uploadPath);
                } else if (pictureName.equals(idCard + "sfz1")) {
                    driverDto.setIdCardFaceImg(uploadPath);
                } else if (pictureName.equals(idCard + "sfz2")) {
                    driverDto.setIdCardBackImg(uploadPath);
                } else if (pictureName.equals(idCard + "sfz3")) {
                    driverDto.setDriverIdCardImg(uploadPath);
                } else if (pictureName.equals(idCard + "jsz1")) {
                    driverDto.setDrivingLicenceHomeImg(uploadPath);
                } else if (pictureName.equals(idCard + "jsz2")) {
                    driverDto.setDrivingLicenceSubImg(uploadPath);
                } else if (pictureName.equals(idCard + "ht")) {
                    driverDto.setContractPhoto(uploadPath);
                } else if (pictureName.equals(idCard + "yhk")) {
                    driverDto.setBankPhoto(uploadPath);
                } else if (pictureName.equals(idCard + "tjbg")) {
                    driverDto.setExaminateReport(uploadPath);
                } else if (pictureName.equals(idCard + "cyzgz")) {
                    driverDto.setCertificateImage(uploadPath);
                } else {
                    errmsg.append("图片命名错误：" + pictureName).append("\r\n");
                }
            }
        }
        // 检查是否全部是必填项了
        String requireColumn = checkRequireColumn(driverDto);
        if (StringUtils.isEmpty(requireColumn)) {
            driverDto.setStatus(DriverConstant.DRIVER_STATUS_NORMAL);
        } else {
            errmsg.append(requireColumn);
        }
        driverService.edit(driverDto);

        String str = errmsg.toString();
        if (StringUtils.isEmpty(str)) {
            return ResponseData.buildSuccessResponse("导入成功", Boolean.TRUE);
        }
        return ResponseData.buildErrorResponse(ResponseData.ERROR_CODE, str);
    }

    private String checkRequireColumn(DriverDto dto) {
        StringBuilder errmsg = new StringBuilder();
        int type = dto.getBusinessType();

        if (StringUtils.isEmpty(dto.getIdCardFaceImg())) {
            errmsg.append("缺少图片：" + dto.getIdCard() + "sfz1.jpg").append("\r\n");
        }
        if (StringUtils.isEmpty(dto.getIdCardBackImg())) {
            errmsg.append("缺少图片：" + dto.getIdCard() + "sfz2.jpg").append("\r\n");
        }
        if (StringUtils.isEmpty(dto.getDrivingLicenceHomeImg())) {
            errmsg.append("缺少图片：" + dto.getIdCard() + "jsz1.jpg").append("\r\n");
        }
        if (StringUtils.isEmpty(dto.getDrivingLicenceSubImg())) {
            errmsg.append("缺少图片：" + dto.getIdCard() + "jsz2.jpg").append("\r\n");
        }
        //出租车司机需要另外检验的图片
        if (type == BUSINESS_TYPE_TAXI) {
            if (StringUtils.isEmpty(dto.getFace())) {
                errmsg.append("缺少图片：" + dto.getIdCard() + "tx.jpg").append("\r\n");
            }
            if (StringUtils.isEmpty(dto.getCertificateImage())) {
                errmsg.append("缺少图片：" + dto.getIdCard() + "cyzgz.jpg").append("\r\n");
            }
        } else {
            if (StringUtils.isEmpty(dto.getDriverIdCardImg())) {
                errmsg.append("缺少图片：" + dto.getIdCard() + "sfz3.jpg").append("\r\n");
            }
        }
        return errmsg.toString();
    }

    @RequestMapping(value = "/export", method = RequestMethod.POST)
    @ApiOperation(value = "导出司机信息", httpMethod = "POST")
    public void exportDriver(@RequestBody AdminDriverPageParam driverPageParam, HttpServletResponse response) {
        try (OutputStream os = response.getOutputStream()) {
            List<DriverExportVo> driverList = driverService.exportAdmin(driverPageParam).getData();
            InputStream in = getClass().getResourceAsStream("/template/driverTemplate.xlsx");
            Workbook workbook = new XSSFWorkbook(in);
            // 获取司机导入格式面板
            Sheet sheet = workbook.getSheetAt(0);
            WorkbookUtil.writeDataToExcelBySheet(sheet, driverList, DriverExportVo.class);

            response.setHeader("Content-Disposition", "attachment; filename=" + java.net.URLEncoder.encode("driver.xlsx", "UTF-8"));
            response.setContentType("application/octet-stream");
            response.setCharacterEncoding("utf-8");
            workbook.write(os);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @RequestMapping(value = "/saveCommissionConfig", method = RequestMethod.POST)
    @ApiOperation(value = "保存司机佣金信息", httpMethod = "POST")
    public ResponseData<Boolean> saveCommissionConfig(@RequestBody DriverCommissionConfigDto driverCommissionConfigDto) {
        return driverService.saveDriverCommissionConfig(driverCommissionConfigDto);
    }

    @RequestMapping(value = "/getCommissionConfig", method = RequestMethod.POST)
    @ApiOperation(value = "获取司机佣金信息", httpMethod = "POST")
    public ResponseData<DriverCommissionConfigDto> saveCommissionConfig(@RequestBody Map<String, String> paraMap) {
        String driverUuid = paraMap.get("driverUuid");
        if (StringUtils.isEmpty(driverUuid)) {
            return ResponseData.buildErrorResponse(ResponseData.ERROR_CODE, "司机ID不能为空");
        }
        return driverService.getDriverCommissionConfig(driverUuid);
    }

    @RequestMapping(value = "/getAppletCode", method = RequestMethod.POST)
    @ApiOperation(value = "生成司机小程序码", httpMethod = "POST")
    public void getAppletCode(@RequestBody Map<String, String> paraMap, HttpServletResponse response) {
        String driverUuid = paraMap.get("driverUuid");
        DriverDto driverDto = driverService.get(driverUuid).getData();
        String redisKey = "driverAppletCodeInfo" + driverDto.getAppid() + driverUuid;
        String imgInfo = redisStringCacheService.get(redisKey);
        if (StringUtils.isEmpty(imgInfo)) {
            String accessToken = getAccessToken(driverDto.getAppid());
            String scene = JsonUtils.toJSONString(MapUtils.build("scene", driverDto.getUuid()));
            String url = "https://api.weixin.qq.com/wxa/getwxacodeunlimit?access_token=" + accessToken;
            imgInfo = sendPost(url, scene);
            redisStringCacheService.set(redisKey, imgInfo, 5);
        }
        ByteArrayInputStream bais = null;
        BufferedOutputStream bos = null;
        BufferedInputStream bis = null;
        try {
            byte[] bytes = Base64Utils.decode(imgInfo);

            bais = new ByteArrayInputStream(bytes);
            bos = new BufferedOutputStream(response.getOutputStream());
            bis = new BufferedInputStream(bais);
            response.setContentType("application/octet-stream;charset=utf-8");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(driverDto.getName() + ".jpg", "utf-8"));
            byte[] buffer = new byte[1024];
            int len;
            while ((len = bis.read(buffer)) != -1) {
                bos.write(buffer, 0, len);
            }
            bos.flush();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            closeIO(bais, bos, bis);
        }
    }

    /**
     * 关闭流
     *
     * @param bais
     * @param bos
     * @param bis
     */
    private void closeIO(ByteArrayInputStream bais, BufferedOutputStream bos, BufferedInputStream bis) {
        try {
            if (bais != null) {
                bais.close();
            }
            if (bos != null) {
                bos.close();
            }
            if (bis != null) {
                bos.close();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取accessToken
     *
     * @return
     */
    private String getAccessToken(String appid) {
        String redisKey = "appletCodeAccessToken" + appid;
        String accessToken = redisStringCacheService.get(redisKey);
        if (StringUtils.isNotEmpty(accessToken)) {
            return accessToken;
        }
        Map<String, Object> resultMap = HttpUtils.get("https://api.weixin.qq.com/cgi-bin/token?grant_type=client_credential&appid=" + WXPAY_CONFIG.getPassengerAppletWxAppid() + "&secret=" + WXPAY_CONFIG.getPassengerAppletWxAppSecret());
        try {
            JsonNode jsonNode = JsonUtils.readNode(resultMap.get("result").toString());
            accessToken = jsonNode.get("access_token").asText();
            redisStringCacheService.set(redisKey, accessToken, 100, TimeUnit.MINUTES);
            return accessToken;
        } catch (Exception e) {
            return null;
        }
    }

    private String sendPost(String url, String param) {
        PrintWriter out = null;
        String result = "";
        InputStream inputStream = null;
        try {
            URL realUrl = new URL(url);
            // 打开和URL之间的连接
            URLConnection conn = realUrl.openConnection();
            // 设置通用的请求属性
            conn.setRequestProperty("Content-Type", "application/json;charset-gbk");
            conn.setRequestProperty("responseType", "arraybuffer");

            // 发送POST请求必须设置如下两行
            conn.setDoOutput(true);
            conn.setDoInput(true);
            // 获取URLConnection对象对应的输出流
            out = new PrintWriter(conn.getOutputStream());
            // 发送请求参数
            out.print(param);
            // flush输出流的缓冲
            out.flush();
            //获取流数据
            inputStream = conn.getInputStream();


            // 将获取流转为base64格式
            byte[] data = null;
            ByteArrayOutputStream swapStream = new ByteArrayOutputStream();
            byte[] buff = new byte[100];
            int rc = 0;
            while ((rc = inputStream.read(buff, 0, 100)) > 0) {
                swapStream.write(buff, 0, rc);
            }
            data = swapStream.toByteArray();

            result = new String(Base64.getEncoder().encode(data));
        } catch (Exception e) {
            e.printStackTrace();
        }
        //使用finally块来关闭输出流、输入流
        finally {
            try {
                if (out != null) {
                    out.close();
                }
                if (inputStream != null) {
                    inputStream.close();
                }

            } catch (IOException ex) {
                ex.printStackTrace();
            }
        }
        return result;
    }
}
